<%@ page language="java" contentType="text/xml; charset=utf-8" pageEncoding="utf-8"%><%@ page import="java.sql.*,java.util.Date,java.text.*" %><%@ page import="org.apache.commons.lang.StringEscapeUtils" %><% 
	
	//날짜 추출
	Date date = new Date();
	SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd");
	String nDate =  simpleDate.format(date);

	// 기본 변수 받기
	String Grade = request.getParameter("Grade");  //권한 구분
	if(Grade == null) Grade = "C";
	String userId = request.getParameter("userId");  //사용자 ID
	if(userId == null) userId = "";
	String type = request.getParameter("Type");  //탭 구분
	if(type == null) type = "TopNews";
	String mode = request.getParameter("mode"); // 2:List , 3:view
	if(mode == null) mode ="2";
	String articleCD = request.getParameter("articleCD"); //게시물 SEQ
	if(articleCD == null) articleCD = "0";
	String ViewRowCount = request.getParameter("ViewRowCount"); //리스트 출력 갯수
	if(ViewRowCount == null) ViewRowCount = "15";
	String Page = request.getParameter("Page");  //현재 페이지
	if(Page == null) Page = "1";
	
	//검색관련 처리
	String second ="";
	String third ="";
	String SearchTitle		= request.getParameter("SearchTitle"); //제목
	if(SearchTitle != null){
		SearchTitle = java.net.URLDecoder.decode(SearchTitle,"utf-8");
	}
	String SearchFirst		= request.getParameter("SearchFirst"); //분야
	String SearchStartDate  = request.getParameter("SearchStartDate"); //시작일
	String SearchEndDate    = request.getParameter("SearchEndDate"); //종료일

	//가지고올 레코드 수
	int TopCount = 0;
		TopCount = Integer.parseInt(ViewRowCount) * Integer.parseInt(Page);


	// 디비 연결
	Connection conn = null;

	try {
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		//실 테이블
		//conn = DriverManager.getConnection ("jdbc:sqlserver://203.225.1.136:1433;DatabaseName=concise", "sa", "zhsTKdltm@12");

		//개발테이블
		conn = DriverManager.getConnection ("jdbc:sqlserver://203.225.1.136:1433;DatabaseName=concise_test", "concisedev", "devconcise");
	} catch (ClassNotFoundException e) {
		  
		System.out.println("connectDB() 상태 업데이트 DB 접속 오류"+ e.toString());
			throw e;
	} catch (SQLException e) {
		System.out.println("connectDB() 상태 업데이트 DB 접속 오류"+ e.toString());
	
		throw e;
	}

	///////////////////////////////////////////
	// 상세 보기
	//////////////////////////////////////////
	if(mode.equals("3")){ // view
	 
	 String Seq		     = "";
     String Title        = "";
	 String INST_DATE    = "";
	 String INFO_SOURCE  = "";
	 String INFO_SEP_HAN = "";
	 String BONMUN       = "";

	 StringBuffer query = new StringBuffer();
	 PreparedStatement pstmt = null;
	 ResultSet rs = null;
	 
	 query.append(" Select B.SEQ, CONVERT(CHAR(10), B.INST_DATE, 102) as INST_DATE, B.INFO_SOURCE, B.INFO_SEP_HAN, B.TITLE, C.BONMUN ");
	 query.append(" From MAIN_1_TAB B, MAIN_5_TAB C Where B.SEQ = C.SEQ ");
	 query.append(" And C.SEQ = ? ");
	 pstmt = conn.prepareStatement(query.toString());
	 pstmt.setString(1,articleCD);
	 rs = pstmt.executeQuery();
	 if(rs.next())
	 {
		Seq 		 = rs.getString("Seq");
		Title 		 = rs.getString("TITLE");
		INST_DATE 	 = rs.getString("INST_DATE");
		INFO_SOURCE  = rs.getString("INFO_SOURCE");
		INFO_SEP_HAN = rs.getString("INFO_SEP_HAN");			
		BONMUN 		 = rs.getString("BONMUN");
	 }
	  
	 
%><?xml version='1.0' encoding='utf-8' ?><NewDataSet>
  <BoardDetail>
    <ArticleCD><%=Seq%></ArticleCD>
    <Title><%=StringEscapeUtils.escapeXml(Title)%></Title>
    <Contents><%=StringEscapeUtils.escapeXml(BONMUN)%></Contents>
    <CreateDate><%=INST_DATE%></CreateDate>
    <Importance><%=INFO_SOURCE%></Importance>
  </BoardDetail>
</NewDataSet><%


	 //이력관리 시작
	 int first_cnt = 0, second_cnt = 0, third_cnt = 0, fourth_cnt = 0;
     if (INFO_SEP_HAN.equals("기업정부")) {
          first_cnt = 1;
     } else if (INFO_SEP_HAN.equals("정치사회")) {
          second_cnt = 1;
     } else if (INFO_SEP_HAN.equals("세계동향")) {
          third_cnt = 1;
     } else if (INFO_SEP_HAN.equals("기획조사")) {
          fourth_cnt = 1;
     }
           
	 //이력테이블 조회		
	 StringBuffer query_history = new StringBuffer();
	 PreparedStatement pstmt_history = null;
	 ResultSet rs_history = null;
	 
	 query_history.append(" SELECT use_hap_cnt FROM query_mng_tab ");
	 query_history.append(" WHERE use_date = cast('"+nDate+"'as datetime)  ");
	 query_history.append(" AND sabun = ? And seq = ? ");

	 pstmt_history = conn.prepareStatement(query_history.toString());
	 pstmt_history.setString(1, userId);
	 pstmt_history.setString(2, articleCD);
	 rs_history = pstmt_history.executeQuery();
	 if(rs_history.next())
	 {
		//이력테이블에 Update 
		PreparedStatement pstmt_Ups = null;
	 	StringBuffer query_Ups = new StringBuffer();
	 	query_Ups.append(" UPDATE query_mng_tab SET first_cnt=?, second_cnt=?, third_cnt=?, fourth_cnt=?, use_hap_cnt=use_hap_cnt+1 ");
	 	query_Ups.append(" WHERE use_date=cast('"+nDate+"' as datetime) AND sabun=? AND seq=? ");

		pstmt_Ups = conn.prepareStatement(query_Ups.toString());
		pstmt_Ups.setInt(1,first_cnt);
		pstmt_Ups.setInt(2,second_cnt);
		pstmt_Ups.setInt(3,third_cnt);
		pstmt_Ups.setInt(4,fourth_cnt);
		pstmt_Ups.setString(5,userId);
		pstmt_Ups.setString(6,articleCD);			
		pstmt_Ups.execute();
 		
 		if(pstmt_Ups!=null) pstmt_Ups.close(); 		 
	   	
	 }else{
	 	//이력테이블에 Insert 
	 	PreparedStatement pstmt_Ins = null;
	 	StringBuffer query_Ins = new StringBuffer();
	 	query_Ins.append(" INSERT INTO query_mng_tab ");
	 	query_Ins.append(" (use_date, sabun, seq, inst_date, info_sep_han, info_grade, first_cnt, second_cnt, third_cnt, fourth_cnt, use_hap_cnt ) ");
	 	query_Ins.append(" VALUES ");
	 	query_Ins.append(" (cast('"+nDate+"' as datetime), ?, ?, cast('"+nDate+"' as datetime), ?, ?, ?, ?, ?, ?, 1 ) ");
		
		pstmt_Ins = conn.prepareStatement(query_Ins.toString());
		pstmt_Ins.setString(1,userId);
		pstmt_Ins.setString(2,articleCD);
		pstmt_Ins.setString(3,INFO_SEP_HAN);
		pstmt_Ins.setString(4,Grade);
		pstmt_Ins.setInt(5,first_cnt);
		pstmt_Ins.setInt(6,second_cnt);
		pstmt_Ins.setInt(7,third_cnt);
		pstmt_Ins.setInt(8,fourth_cnt);
		pstmt_Ins.execute();
 		
		if(pstmt_Ins!=null) pstmt_Ins.close();
	 }
	 //이력관리 종료
///////////////////////////////////////////////////////////////
	}else{ 
///////////////////////////////////////////////////////////////
	///////////////////////////////////////////
	// 리스트 보기
	//////////////////////////////////////////
	 int TotalCnt = 0;
	
	 StringBuffer query = new StringBuffer();
	 PreparedStatement pstmt = null;
	 ResultSet rs = null;
	 
	 query.append(" Select TOP "+TopCount+"  CONVERT(CHAR(10), INST_DATE, 102) as INST_DATE, SEQ, INFO_SOURCE, MADE_NAME, MADE_SOSOK, INFO_GRADE, UPDATE_DATE, TITLE");
	 query.append(" From MAIN_1_TAB Where 1=1 ");
	 
	 if(type.equals("CeoInfo")){ //CeoInfo
		if(SearchTitle.equals("")){  //검색이 아니면, 기본 날짜 검색
			query.append(" AND (inst_date between cast('2011-07-01' as datetime) and cast('"+nDate+"' as datetime)) ");
	    }
		query.append(" AND reliable=3  AND top_news=1 AND info_source='기획조사'  AND info_grade  >= '"+Grade+"'      ");
		
	 }else if(type.equals("Manage")){ //Manage
		if(SearchTitle.equals("")){  //검색이 아니면, 기본 날짜 검색
			query.append(" AND (inst_date BETWEEN cast('2008-07-01' as datetime) and cast('"+nDate+"' as datetime)) ");
		}
		query.append(" And RELIABLE = 1  AND info_grade  >= '"+Grade+"'  ");

	 }else{  //TopNews
		if(SearchTitle.equals("")){  //검색이 아니면, 기본 날짜 검색
			query.append(" And inst_date between cast('2009-07-01' as datetime) and cast('"+nDate+"' as datetime)  ");
		}
		query.append(" And (RELIABLE = 3 OR RELIABLE = 1)  AND TOP_NEWS = 1 AND info_grade  >= '"+Grade+"' ");
	 }

	 //검색 공통 쿼리
	 if(!SearchTitle.equals("")){
		query.append(" AND (inst_date BETWEEN cast('"+SearchStartDate+"' as datetime) and cast('"+SearchEndDate+"' as datetime)) ");
		query.append(" And TITLE LIKE '%"+SearchTitle+"%' ");

			if(SearchFirst.equals("3")) { //정치사회
                query.append( " AND INFO_SEP_HAN = '정치사회' ");// AND LRG_SEP_HAN LIKE '%" + second + "%'
            } else if(SearchFirst.equals("2")) {//정부
                query.append(" AND LRG_SEP_HAN='정부부처' "); //AND MID_SEP_HAN LIKE '%" + second + "%' 
            } else if(SearchFirst.equals("1")) { //기업
                query.append(" AND LRG_SEP_HAN='그룹기업'  "); //AND MID_SEP_HAN LIKE '%" + second + "%' AND SML_SEP_HAN LIKE '%" + third + "%'
            }
	 }
	 query.append(" ORDER BY INST_DATE DESC, UPDATE_DATE DESC, SEQ DESC ");
	 pstmt = conn.prepareStatement(query.toString());
	 rs = pstmt.executeQuery();
		
%><?xml version='1.0' encoding='utf-8' ?><NewDataSet>
<%
	 while(rs.next()){
		out.println("<BoardList>");
		out.println("	<ArticleCD>"+rs.getString("SEQ")+"</ArticleCD>");
		out.println("	<Importance>"+rs.getString("INFO_SOURCE")+"</Importance>");
		out.println("	<InfoGrade>"+rs.getString("INFO_GRADE")+"</InfoGrade>");
		out.println("	<UserName>"+rs.getString("MADE_NAME")+"</UserName>");
		out.println("	<Title>"+StringEscapeUtils.escapeXml(rs.getString("TITLE"))+"</Title>");
		out.println("	<UCreateDate>"+rs.getString("INST_DATE")+"</UCreateDate>");
		out.println("</BoardList>");
		TotalCnt ++;
	 }

	if(rs != null) try { rs.close(); } catch(SQLException se) {}
	if(pstmt != null) try { pstmt.close(); } catch(SQLException se) {}

	 //Count 체크 쿼리
	 String nLTotalRecord = "0";
	 StringBuffer query2 = new StringBuffer();
	 PreparedStatement pstmt2 = null;
	 ResultSet rs2 = null;
	 query2.append(" Select count(*) as cnt ");
	 query2.append(" From MAIN_1_TAB Where 1=1 ");
	 
	 if(type.equals("CeoInfo")){ //CeoInfo
		if(SearchTitle.equals("")){  //검색이 아니면, 기본 날짜 검색
			query2.append(" AND (inst_date between cast('2011-07-01' as datetime) and cast('"+nDate+"' as datetime)) ");
	    }
		query2.append(" AND reliable=3  AND top_news=1 AND info_source='기획조사'  AND info_grade  >= '"+Grade+"'      ");
		
	 }else if(type.equals("Manage")){ //Manage
		if(SearchTitle.equals("")){  //검색이 아니면, 기본 날짜 검색
			query2.append(" AND (inst_date BETWEEN cast('2008-07-01' as datetime) and cast('"+nDate+"' as datetime)) ");
		}
		query2.append(" And RELIABLE = 1  AND info_grade  >= '"+Grade+"'  ");

	 }else{  //TopNews
		if(SearchTitle.equals("")){ //검색이 아니면, 기본 날짜 검색
			query2.append(" And inst_date between cast('2009-07-01' as datetime) and cast('"+nDate+"' as datetime)  ");
		}
		query2.append(" And (RELIABLE = 3 OR RELIABLE = 1)  AND TOP_NEWS = 1 AND info_grade  >= '"+Grade+"' ");
	 }

	 //검색 공통 쿼리
	 if(!SearchTitle.equals("")){
		query2.append(" AND (inst_date BETWEEN cast('"+SearchStartDate+"' as datetime) and cast('"+SearchEndDate+"' as datetime)) ");
		query2.append(" And TITLE LIKE '%"+SearchTitle+"%' ");

			if(SearchFirst.equals("3")) { //정치사회
                query2.append( " AND INFO_SEP_HAN = '정치사회' ");// AND LRG_SEP_HAN LIKE '%" + second + "%'
            } else if(SearchFirst.equals("2")) { //정부
                query2.append(" AND LRG_SEP_HAN='정부부처' "); //AND MID_SEP_HAN LIKE '%" + second + "%' 
            } else if(SearchFirst.equals("1")) { //기업
                query2.append(" AND LRG_SEP_HAN='그룹기업'  "); //AND MID_SEP_HAN LIKE '%" + second + "%' AND SML_SEP_HAN LIKE '%" + third + "%'
            }
	 }
	 pstmt2 = conn.prepareStatement(query2.toString());
	 rs2 = pstmt2.executeQuery();
	 if(rs2.next()) {
	  nLTotalRecord = rs2.getString("cnt");
	}
%>
  <TableInfo>
    <TotalRecordCnt><%=nLTotalRecord%></TotalRecordCnt>
    <CurrCnt><%=TopCount%></CurrCnt>
	<CurrPage><%=Page%></CurrPage>
	<ViewRowCount><%=ViewRowCount%></ViewRowCount>
	<Query><%=query.toString()%></Query>	
  </TableInfo>
</NewDataSet>
<% 
	if(rs2 != null) try { rs2.close(); } catch(SQLException se) {}
	if(pstmt2 != null) try { pstmt2.close(); } catch(SQLException se) {}	
} 
%><%
////////////////////// DB CLOSE ////////////////////////////
///////////////////////////////////////////////////////////
if(conn != null) {
	try {
		conn.close();
	} catch (Exception e) {
	}
}
conn = null;
///////////////////////////////////////////////////////////
%>